from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

engine = create_engine("mysql+pymysql://admin:password@192.168.0.129/test?charset=utf8mb4")
Base = declarative_base()

DBSession = sessionmaker(bind=engine)

session = DBSession()


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(64), nullable=False)
    password = Column(String(64), nullable=False)
    # server_default: 对于数据库原有数据，该列使用default默认值填充，新增的数据该列不能为空
    # server_default: 会修改表结构
    # server_default: 对于整型或者布尔型设置时需要特殊处理
    # from sqlalchemy import text
    # is_del = Column(Boolean,server_default=text('False'))
    # sit_num = Column(Integer,server_default=text('80'))
    # default：对于数据库原有数据不做处理，新增的数据该列不能为空
    # default：不会修改表结构
    real_name = Column(String(64), nullable=False, server_default='default')
    email = Column(String(64), nullable=False)
    group_id = Column(Integer, ForeignKey('groups.id', ondelete='CASCADE'), nullable=False)
    # 此处添加backref，指明反向查询时用到的名称，在Group表中就不用在写relationship了
    group = relationship('Group', backref='user')

    def __repr__(self):
        return f"{self.__class__.__name__}(username: {self.username} | group: {self.group})"


permission_group_mapping = Table(
    "perms_group",
    Base.metadata,
    Column('permissions_id', Integer, ForeignKey('permissions.id'), primary_key=True),
    Column('groups_id', Integer, ForeignKey('groups.id'), primary_key=True)
)


class Group(Base):
    __tablename__ = 'groups'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    # 此处加了这个关联关系，就可以通过group对象.user获取组内的所有用户
    # user = relationship(User)

    def __repr__(self):
        return f"{self.__class__.__name__}(name: {self.name})"


class Permission(Base):
    __tablename__ = 'permissions'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    desc = Column(String(256), nullable=False)

    def __repr__(self):
        return f"{self.__class__.__name__}(name: {self.name})"
